﻿using System;
using System.Collections.Generic;
using System.Text;
using PS.DBUtility;
using System.Data;

namespace PS.DAL
{
    public class EmpList
    {

        /// <summary>
        /// 获取人员信息(从10.101.103.170 SQL SERVER 2000 的数据库中获取)
        /// </summary>
        /// <returns></returns>
        public DataSet GetList(string name, string dept, string empType, string zc, long pageSize, long pageIndex, out long totalCount)
        {
            using (DBHelperRS rs = new DBHelperRS())
            {
                string wherestr = "";
                if (name != "")
                    wherestr += " and XM like '%" + name + "%'";
                if (dept != "")
                    wherestr += " and jb.DWBM = '" + dept + "'";
                if (empType != "")
                    wherestr += " and LBMC like '%" + empType + "%'";
                if (zc != "")
                    wherestr += " and ZC like '%" + zc + "%'";
                string sqlcount = string.Format("select count(1) from RS_JBXX jb where 1=1 " + wherestr);

                totalCount = long.Parse(DBHelperRS.ExecuteScalar(sqlcount).ToString());

                StringBuilder strSql = new StringBuilder();
                strSql.Append("  select * from  (select top " + totalCount + " * from ( select top ");
                strSql.Append(pageSize * pageIndex <= totalCount ? pageSize : totalCount - pageSize * (pageIndex - 1));
                strSql.Append(" datediff(year,CSRQ,getdate()) AGE,DWMC, jgsf.JGMC GJSF,jgsx.JGMC GJSX, ");
                strSql.Append(" RYBH, jb.DWBM, GWXZ, LBMC, ZZMM,convert(varchar(10),ZCJSSJ,121) GRZZSJ, MZMC, ZBMC, GWMC,");
                strSql.Append(" GRJSDJ, QYZB, WHCD, SXXS, HTLB, GWZT, GZH, XM, XBMC, convert(varchar(10),CSRQ,121)CSRQ, NL,");
                strSql.Append(" HF=case when HF<>'Y' then '未婚' else '已婚' end, SFZ, JGSF, JGSX, ");
                strSql.Append(" JG, convert(varchar(10),PDSJ,121) PDSJ, ZC,convert(varchar(10),ZCJSSJ,121)  ZCJSSJ, convert(varchar(10),SGSJ,121)SGSJ,");
                strSql.Append(" convert(varchar(10),JSDJTGSJ,121) JSDJTGSJ, convert(varchar(10),QYZBPWSJ,121)QYZBPWSJ, DHYZ, SLCD, BYWX, SXZY, ");
                strSql.Append("  convert(varchar(10),BYSJ,121)BYSJ, convert(varchar(10),FPSJ,121)FPSJ,convert(varchar(10),CJGZSJ,121) CJGZSJ,");
                strSql.Append(" convert(varchar(10),LYHSJ,121) LYHSJ, HTBH,convert(varchar(10),QDHTSJ,121) QDHTSJ,convert(varchar(10),HTQX,121) HTQX,");
                strSql.Append(" convert(varchar(10),JCSJ,121)JCSJ, SHBXH, JTZZ, LXDH, ");
                strSql.Append(" POXM, PODW,convert(varchar(10),TXSJ,121) TXSJ, BZ, YXF=case when jb.HF<>'Y' then '无效' else '有效' end, XP, jb.BZPY, jb.WBZX, yhzh, jfjs, jdgz, xzzw, xl, zylb, ");
                strSql.Append(" convert(varchar(10),jcldhtsj,121)jcldhtsj,convert(varchar(10),lcsj,121) lcsj, BZZW, ybbh from dbo.RS_JBXX jb ");
                strSql.Append(" left join dbo.BM_DW d on jb.DWBM=d.DWBM ");
                strSql.Append(" left join BM_RS_JGBM jgsf on jb.JGSF=jgsf.JGBM ");
                strSql.Append(" left join BM_RS_JGBM jgsx on jb.JGSX=jgsx.JGBM ");
                strSql.Append(" where jb.rybh not in (select top ");
                strSql.Append((pageIndex - 1) * pageSize);
                strSql.Append(" rybh from RS_JBXX jb left join dbo.BM_DW d on jb.DWBM=d.DWBM where 1=1");
                strSql.Append(wherestr);
                strSql.Append(" order by RYBH)");
                strSql.Append(wherestr);
                strSql.Append(" order by RYBH)a  ");
                strSql.Append(" order by RYBH)a  ");

                return DBHelperRS.Query(strSql.ToString());
            }
        }

        /// <summary>
        /// 获取人员类别
        /// </summary>
        public DataSet getEmpType()
        {
            using (DBHelperRS rs = new DBHelperRS())
            {
                StringBuilder sb = new StringBuilder();

                sb.Append("select * from dbo.BM_RS_LBMC order by xssx");

                return DBHelperRS.Query(sb.ToString());
            }
        }

        /// <summary>
        /// 获取职称
        /// </summary>
        public DataSet getZC()
        {
            using (DBHelperRS rs = new DBHelperRS())
            {
                StringBuilder sb = new StringBuilder();

                sb.Append("select * from BM_RS_ZWBM");

                return DBHelperRS.Query(sb.ToString());
            }
        }

        /// <summary>
        /// 获取某个人员的基本信息
        /// </summary>
        /// <param name="RYBH"></param>
        /// <returns></returns>
        public DataSet getJBXXOne(string RYBH)
        {
            using (DBHelperRS rs = new DBHelperRS())
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("  select  ");
                strSql.Append(" datediff(year,CSRQ,getdate()) AGE,DWMC, jgsf.JGMC GJSF,jgsx.JGMC GJSX, ");
                strSql.Append(" RYBH, jb.DWBM, GWXZ, LBMC, ZZMM,convert(varchar(10),ZCJSSJ,121) GRZZSJ, MZMC, ZBMC, GWMC,");
                strSql.Append(" GRJSDJ, QYZB, WHCD, SXXS, HTLB, GWZT, GZH, XM, XBMC, convert(varchar(10),CSRQ,121)CSRQ, NL,");
                strSql.Append(" HF=case when HF<>'Y' then '未婚' else '已婚' end, SFZ, JGSF, JGSX, ");
                strSql.Append(" JG, convert(varchar(10),PDSJ,121) PDSJ, ZC,convert(varchar(10),ZCJSSJ,121)  ZCJSSJ, convert(varchar(10),SGSJ,121)SGSJ,");
                strSql.Append(" convert(varchar(10),JSDJTGSJ,121) JSDJTGSJ, convert(varchar(10),QYZBPWSJ,121)QYZBPWSJ, DHYZ, SLCD, BYWX, SXZY, ");
                strSql.Append("  convert(varchar(10),BYSJ,121)BYSJ, convert(varchar(10),FPSJ,121)FPSJ,convert(varchar(10),CJGZSJ,121) CJGZSJ,");
                strSql.Append(" convert(varchar(10),LYHSJ,121) LYHSJ, HTBH,convert(varchar(10),QDHTSJ,121) QDHTSJ,convert(varchar(10),HTQX,121) HTQX,");
                strSql.Append(" convert(varchar(10),JCSJ,121)JCSJ, SHBXH, JTZZ, LXDH, ");
                strSql.Append(" POXM, PODW,convert(varchar(10),TXSJ,121) TXSJ, BZ, YXF=case when jb.HF<>'Y' then '无效' else '有效' end, XP, jb.BZPY, jb.WBZX, yhzh, jfjs, jdgz, xzzw, xl, zylb, ");
                strSql.Append(" convert(varchar(10),jcldhtsj,121)jcldhtsj,convert(varchar(10),lcsj,121) lcsj, BZZW, ybbh from dbo.RS_JBXX jb ");
                strSql.Append(" left join dbo.BM_DW d on jb.DWBM=d.DWBM ");
                strSql.Append(" left join BM_RS_JGBM jgsf on jb.JGSF=jgsf.JGBM ");
                strSql.Append(" left join BM_RS_JGBM jgsx on jb.JGSX=jgsx.JGBM ");
                strSql.Append(" where jb.rybh ='" + RYBH + "' ");

                return DBHelperRS.Query(strSql.ToString());
            }
        }

        /// <summary>
        /// 获取某个人员的基本信息
        /// </summary>
        /// <param name="RYBH"></param>
        /// <returns></returns>
        public DataSet getJBXXOneForSalary(string RYBH)
        {
            using (DBHelperRS rs = new DBHelperRS())
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("  select  ");
                strSql.Append("  jb.rybh,jb.xm,jb.xbmc, d.dwmc as DeptName ,comp.dwmc as companyName,convert(varchar(10),CSRQ,121) CSRQ, ");
                strSql.Append(" convert(varchar(10),CJGZSJ,121) CJGZSJ,  jdgz  ,zc from dbo.RS_JBXX jb  ");
                strSql.Append(" left join dbo.BM_DW d on jb.DWBM=d.DWBM left join  dbo.BM_DW comp on d.SJBM LIKE '%'+comp.DWBM+'%' AND comp.sjbm='&' ");

                strSql.Append(" where jb.rybh ='" + RYBH + "' ");

                return DBHelperRS.Query(strSql.ToString());

            }
        }

        //        select jb.rybh,jb.xm,jb.xbmc, d.dwmc as DeptName ,comp.dwmc as companyName,convert(varchar(10),CSRQ,121) CSRQ,
        //convert(varchar(10),CJGZSJ,121) CJGZSJ,  jdgz  ,zc
        // from dbo.RS_JBXX jb  
        //left join dbo.BM_DW d on jb.DWBM=d.DWBM
        //left join  dbo.BM_DW comp on d.SJBM LIKE '%'+comp.DWBM+'%' AND comp.sjbm='&'
        // where rybh=5
        /// <summary>
        /// 获取某个人员的基本信息
        /// </summary>
        /// <param name="RYBH"></param>
        /// <returns></returns>
        public DataSet getJBXXAllForSalary(string RYBHs)
        {
            if (string.IsNullOrEmpty(RYBHs))
            {
                return new DataSet();
            }
            using (DBHelperRS rs = new DBHelperRS())
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("  select  ");
                strSql.Append("  jb.sfz,jb.rybh,jb.xm,jb.xbmc, d.dwmc as DeptName ,comp.dwmc as companyName,convert(varchar(10),CSRQ,121) CSRQ, ");
                strSql.Append(" convert(varchar(10),CJGZSJ,121) CJGZSJ,  jdgz  ,zc from dbo.RS_JBXX jb  ");
                strSql.Append(" left join dbo.BM_DW d on jb.DWBM=d.DWBM left join  dbo.BM_DW comp on d.SJBM LIKE '%'+comp.DWBM+'%' AND comp.sjbm='&' ");
               
                strSql.Append(" where jb.rybh in  (" + RYBHs + ") ");

                return DBHelperRS.Query(strSql.ToString());

            }
        }
        /// <summary>
        /// 获取个人简历
        /// </summary>
        public DataSet getGRJL(string RYBH)
        {
            using (DBHelperRS rs = new DBHelperRS())
            {
                StringBuilder sb = new StringBuilder();

                sb.Append("select * from dbo.RS_GRJL where rybh='" + RYBH + "'");

                return DBHelperRS.Query(sb.ToString());
            }
        }

        /// <summary>
        /// 获取社会关系
        /// </summary>
        public DataSet getSHGX(string RYBH)
        {
            using (DBHelperRS rs = new DBHelperRS())
            {
                StringBuilder sb = new StringBuilder();

                sb.Append("select * from dbo.RS_SHGX where rybh='" + RYBH + "'");

                return DBHelperRS.Query(sb.ToString());
            }
        }

        /// <summary>
        /// 获取学历变更
        /// </summary>
        public DataSet getXLBG(string RYBH)
        {
            using (DBHelperRS rs = new DBHelperRS())
            {
                StringBuilder sb = new StringBuilder();

                sb.Append("select * from dbo.RS_XLBG where rybh='" + RYBH + "'");

                return DBHelperRS.Query(sb.ToString());
            }
        }

        /// <summary>
        /// 获取职称变更
        /// </summary>
        public DataSet getZCBG(string RYBH)
        {
            using (DBHelperRS rs = new DBHelperRS())
            {
                StringBuilder sb = new StringBuilder();

                sb.Append("select * from dbo.RS_JSZW where rybh='" + RYBH + "'");

                return DBHelperRS.Query(sb.ToString());
            }
        }

        /// <summary>
        /// 获取特种作业
        /// </summary>
        public DataSet getTZZY(string RYBH)
        {
            using (DBHelperRS rs = new DBHelperRS())
            {
                StringBuilder sb = new StringBuilder();

                sb.Append("select * from dbo.RS_TZZY where rybh='" + RYBH + "'");

                return DBHelperRS.Query(sb.ToString());
            }
        }

        /// <summary>
        /// 获取职业技能
        /// </summary>
        public DataSet getZYJN(string RYBH)
        {
            using (DBHelperRS rs = new DBHelperRS())
            {
                StringBuilder sb = new StringBuilder();

                sb.Append("select * from dbo.RS_ZYJN where rybh='" + RYBH + "'");

                return DBHelperRS.Query(sb.ToString());
            }
        }

        /// <summary>
        /// 获取子部门数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataSet GetDeptList(string parentID)
        {
            using (DBHelperRS rs = new DBHelperRS())
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select a.* ");
                strSql.Append(" from BM_DW a where a.YXF='Y' ");
                strSql.Append(" and a.SJBM = '").Append(parentID).Append("' ");
                return DbHelperSQL.Query(strSql.ToString());
            }
        }

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dept"></param>
        /// <param name="empType"></param>
        /// <param name="zc"></param>
        /// <returns></returns>
        public DataSet GetList(string name, string dept, string empType, string zc)
        {
            using (DBHelperRS rs = new DBHelperRS())
            {
                string wherestr = "";
                if (name != "")
                    wherestr += " and XM like '%" + name + "%'";
                if (dept != "")
                    wherestr += " and jb.DWBM = '" + dept + "'";
                if (empType != "")
                    wherestr += " and LBMC like '%" + empType + "%'";
                if (zc != "")
                    wherestr += " and ZC like '%" + zc + "%'";


                StringBuilder strSql = new StringBuilder();
                strSql.Append("  select ");
                strSql.Append(" datediff(year,CSRQ,getdate()) AGE,DWMC, jgsf.JGMC GJSF,jgsx.JGMC GJSX, ");
                strSql.Append(" RYBH, jb.DWBM, GWXZ, LBMC, ZZMM,convert(varchar(10),ZCJSSJ,121) GRZZSJ, MZMC, ZBMC, GWMC,");
                strSql.Append(" GRJSDJ, QYZB, WHCD, SXXS, HTLB, GWZT, GZH, XM, XBMC, convert(varchar(10),CSRQ,121)CSRQ, NL,");
                strSql.Append(" HF=case when HF<>'Y' then '未婚' else '已婚' end, SFZ, JGSF, JGSX, ");
                strSql.Append(" JG, convert(varchar(10),PDSJ,121) PDSJ, ZC,convert(varchar(10),ZCJSSJ,121)  ZCJSSJ, convert(varchar(10),SGSJ,121)SGSJ,");
                strSql.Append(" convert(varchar(10),JSDJTGSJ,121) JSDJTGSJ, convert(varchar(10),QYZBPWSJ,121)QYZBPWSJ, DHYZ, SLCD, BYWX, SXZY, ");
                strSql.Append("  convert(varchar(10),BYSJ,121)BYSJ, convert(varchar(10),FPSJ,121)FPSJ,convert(varchar(10),CJGZSJ,121) CJGZSJ,");
                strSql.Append(" convert(varchar(10),LYHSJ,121) LYHSJ, HTBH,convert(varchar(10),QDHTSJ,121) QDHTSJ,convert(varchar(10),HTQX,121) HTQX,");
                strSql.Append(" convert(varchar(10),JCSJ,121)JCSJ, SHBXH, JTZZ, LXDH, ");
                strSql.Append(" POXM, PODW,convert(varchar(10),TXSJ,121) TXSJ, BZ, YXF=case when jb.HF<>'Y' then '无效' else '有效' end, XP, jb.BZPY, jb.WBZX, yhzh, jfjs, jdgz, xzzw, xl, zylb, ");
                strSql.Append(" convert(varchar(10),jcldhtsj,121)jcldhtsj,convert(varchar(10),lcsj,121) lcsj, BZZW, ybbh from dbo.RS_JBXX jb ");
                strSql.Append(" left join dbo.BM_DW d on jb.DWBM=d.DWBM ");
                strSql.Append(" left join BM_RS_JGBM jgsf on jb.JGSF=jgsf.JGBM ");
                strSql.Append(" left join BM_RS_JGBM jgsx on jb.JGSX=jgsx.JGBM ");
                strSql.Append(" where 1=1");
                strSql.Append(wherestr);
                strSql.Append(" order by RYBH");

                return DBHelperRS.Query(strSql.ToString());
            }
        }

        /// <summary>
        /// 分页查询人员信息
        /// </summary>
        /// <param name="PersonName">人员名称，模糊查询</param>
        /// <param name="pageSize">每页行数</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="totalCount">返回总行数</param>
        /// <returns></returns>
        public DataSet GetList(string PersonName, string DepID, long pageSize, long pageIndex, out long totalCount)
        {
            using (DBHelperRS rs = new DBHelperRS())
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" select   a.rybh as PersonID ,a.XM as  PersonName ,a.xbmc as PersonSex1,a.DWBM as DepID,bm.dwmc as DepName1 ");
                strSql.Append(" from RS_JBXX a  left join BM_DW bm on a.DWBM=bm.DWBM  ");

                StringBuilder sb1 = new StringBuilder();
                if (!string.IsNullOrEmpty(PersonName))
                {
                    sb1.Append(" and a.XM like '%").Append(PersonName).Append("%'");
                }
                if (!string.IsNullOrEmpty(DepID))
                {
                    sb1.Append(" and a.DWBM  like '%").Append(DepID).Append("%'");
                }
                if (!string.IsNullOrEmpty(sb1.ToString()))
                {
                    strSql.Append(" where 1=1 " + sb1.ToString());
                }

                strSql.Append("  order by a.PersonID");

                string totalSql = PageSplitHelper.GetTotalSql(strSql.ToString());
                totalCount = long.Parse(DbHelperSQL.ExecuteScalar(totalSql).ToString());

                string pageSql = PageSplitHelper.GetPageSql(strSql.ToString(), pageSize, pageIndex, totalCount);


                return DbHelperSQL.Query(pageSql,new  System.Data.SqlClient.SqlParameter[0]);
            }
        }

    }
}
